clear all
set more off

global rawdata "data directory here" 
global output "output directory here"

clear
cd $rawdata
import delimited using ce1997.csv
 
rename v1 year
rename v2 geo 
rename v7 ind 
rename v18 totalw_m
rename v19 totalw_f
 
gen bank_v1 = 0 
replace bank_v1 = 1 if ind==651

drop if ind != 651
drop v*

cd $output
save sample1997,replace

 
clear
cd $rawdata
import delimited using ce1998.csv
 
rename v1 year
rename v2 geo 
rename v8 ind 
rename v19 totalw_m
rename v20 totalw_f
 
gen bank_v1 = 0 
replace bank_v1 = 1 if ind==651

drop if ind != 651
drop v*

cd $output
save sample1998,replace


clear
cd $output
use sample1997 
append using sample1998 
  
egen geo_id = group(geo)  
sum geo_id,d
  
bys year geo_id: egen bank_sum = total(bank_v1) 
  
keep year geo geo_id bank_sum  
duplicates drop 
  
rename year yr
bys geo_id yr: gen tag_geo_yr = _n

keep if tag_geo_yr == 1
keep geo bank_sum yr 
 
save bank_data, replace 


clear
cd $output
use sample1997 
append using sample1998 
 
egen geo_id = group(geo)  
sum geo_id,d

gen totalw = totalw_m + totalw_f   
bys year geo_id: egen bankworker_sum = total(totalw) 

keep year geo geo_id bankworker_sum  
duplicates drop 
 
rename year yr
bys geo_id yr: gen tag_geo_yr = _n

keep if tag_geo_yr == 1
keep geo bankworker_sum yr
 
save bankworker_data, replace 
  

clear
cd $output
use bank_data

gen xx = bank_sum if yr == 1997
bys geo: egen B1997 = max(xx)
drop xx

gen xx = bank_sum if yr == 1998
bys geo: egen B1998 = max(xx)
drop xx 
    
gen dbanks = ln(B1997/B1998) 
 
keep if yr == 1998
keep geo dbanks B1997 B1998
duplicates drop
tostring geo, replace 
save geo_bankshock, replace
















 
